﻿
namespace CustomDataService
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;
    using System.Linq;
    using System.ServiceModel.DomainServices.Hosting;
    using System.ServiceModel.DomainServices.Server;
    using LightSwitchApplication.Implementation;
    //using ApplicationData.Implementation;
    using System.Data.EntityClient;
    using System.Web.Configuration;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Data;

    // This class is used as the class that is returned
    // This can have any 'shape' you desire
    // Make sure this is outside of the WCF_RIA_Service class
    // but inside the WCF_RIA_Project namespace
    public class TraingroupConcatProperties
    {
        [Key]
        public int TraingroupID { get; set; }
        public string concatString { get; set; }
    }
    
    // TODO: Create methods containing your application logic.
    // TODO: add the EnableClientAccessAttribute to this class to expose this DomainService to clients.
    public class CustomDataService : DomainService
    {
        // This Context property is code that connects to the LightSwitch database
        // The code in the Database connection region can be reused as it is 
        #region Database connection
        private ApplicationData m_context;
        public ApplicationData Context
        {
            get
            {
                if (this.m_context == null)
                {
                    string connString =
                        System.Web.Configuration.WebConfigurationManager
                        .ConnectionStrings["_IntrinsicData"].ConnectionString;
                    EntityConnectionStringBuilder builder = new EntityConnectionStringBuilder();
                    builder.Metadata =
                        "res://*/ApplicationData.csdl|res://*/ApplicationData.ssdl|res://*/ApplicationData.msl";
                    builder.Provider =
                        "System.Data.SqlClient";
                    builder.ProviderConnectionString = connString;
                    this.m_context = new ApplicationData(builder.ConnectionString);
                }
                return this.m_context;
            }
        }
        #endregion

        protected override int Count<T>(IQueryable<T> query)
        {
            return query.Count();
        }

        
        [Query(IsDefault = true)]
        public IQueryable<TraingroupConcatProperties> Default()
        {
            List<TraingroupConcatProperties> resultList = new List<TraingroupConcatProperties>();
            TraingroupConcatProperties resultEntry = new TraingroupConcatProperties();
            resultEntry.TraingroupID = 0;
            resultEntry.concatString = "Invalid Query";
            resultList.Add(resultEntry);
            return resultList.AsQueryable();
        }
        
        [Query(IsDefault = false)]
        public IQueryable<TraingroupConcatProperties> GetStakeholderPropertyStringConcat(int? traingroupId, int? propertyId)
        {
            List<TraingroupConcatProperties> resultList = new List<TraingroupConcatProperties>();
            TraingroupConcatProperties resultEntry = new TraingroupConcatProperties();
            resultEntry.TraingroupID = traingroupId.HasValue ? traingroupId.Value : 0;

            if (!traingroupId.HasValue || !propertyId.HasValue)
            {
                resultEntry.TraingroupID = 0;
                resultEntry.concatString = "Invalid Query";
                resultList.Add(resultEntry);
                return resultList.AsQueryable();
            }

            using (SqlConnection connection = new SqlConnection())
            {
                // Todo: Find a way to get the connectionStingName - not just guessing it right.
                string connectionStringName = "_IntrinsicData"; //Maybe this won't work in deployed app
                connection.ConnectionString = System.Web.Configuration.WebConfigurationManager
                        .ConnectionStrings[connectionStringName].ConnectionString;

                string procedure = "dbo.spGetStakeholderPropertiesForTraingroup";

                resultEntry.concatString = "";
                using (SqlCommand command = new SqlCommand(procedure, connection))  // Fire StoredProcedure
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add(new SqlParameter("@TraingroupId", traingroupId));
                    command.Parameters.Add(new SqlParameter("@StaticCustomPropertyId", propertyId));

                    SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

                    DataSet dataSet = new DataSet();
                    dataAdapter.Fill(dataSet, "result");

                    DataTable dataTable = dataSet.Tables["result"];

                    foreach (DataRow row in dataTable.Rows)
                    {
                        resultEntry.concatString += row["CustomPropertyValue"];
                        if (row != dataTable.Rows[dataTable.Rows.Count - 1])
                            resultEntry.concatString += "; ";
                    }
                }
                resultList.Add(resultEntry);
                return resultList.AsQueryable();
            }
        }
    }
}


